package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.google.gson.Gson;

public class Station_DB {
//	public static void main(String[] args) {
//		List<HashMap<String, String>> restList=new ArrayList<HashMap<String,String>>();
//		Station_DB station_DB=new Station_DB();
//		restList=station_DB.returnByPanfu("hyks", "2020-11-23", "R:");
//		Gson gson=new Gson();
//		String json=gson.toJson(restList);
//		System.out.println(json);
//	}

	// 台站工况数据库查询层（app顶部状态预警图片返回数据）
	public List<HashMap<String, String>> returnResult(String spot_name, String refresh_time) {
		Connection con = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql://182.92.239.30:3306/ks?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
		String user = "root";
		String password = "root";
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		// 选取数据库方法
//			System.out.println("进入数据库这里的数据："+"spot_name:"+spot_name+"sel_year:"+sel_year+"sel_month"+sel_month);
//			String sql=sel_db(spot_name, sel_year, sel_month);
		// 测试使用
//			spot_name="hyks";
		System.out.println("进入数据库这里的数据：" + "spot_name:" + spot_name + "refresh_time:" + refresh_time);
		String sql = sel_data_bytime(spot_name, refresh_time);
		Gson gson = new Gson();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);

		} catch (Exception e) {
			System.out.println("数据库连接失败");
			e.printStackTrace();
		}
		try {
//				preparedStatement=con.prepareStatement("select * from 5mine_quack_results where type like '%"+dataString+"%'");
			preparedStatement = con.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("panfu", resultSet.getString(2));
				map.put("location", resultSet.getString(3));
				map.put("status", resultSet.getString(7));
				map.put("unused", resultSet.getString(8));
				map.put("used", resultSet.getString(9));
				map.put("total", resultSet.getString(10));
				map.put("netspeed", resultSet.getString(11));
				list.add(map);
			}
		} catch (Exception e) {
			System.out.println("连接失败");
			e.printStackTrace();
		}
		// 打印json
		String json = gson.toJson(list);
		System.out.println(json);
		return list;
	}

	// 台站工况数据库查询层——（通过盘符返回具体情况）
	public List<HashMap<String, String>> returnByPanfu(String spot_name, String refresh_time, String panfu) {
		Connection con = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String driver = "com.mysql.cj.jdbc.Driver";
		String url = "jdbc:mysql://182.92.239.30:3306/ks?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
		String user = "root";
		String password = "root";
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		System.out
				.println("进入数据库这里的数据：" + "spot_name:" + spot_name + "refresh_time:" + refresh_time + "panfu:" + panfu);
		String sql = sel_data_bytimeandpanfu(spot_name, refresh_time, panfu);
		Gson gson = new Gson();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);

		} catch (Exception e) {
			System.out.println("数据库连接失败");
			e.printStackTrace();
		}
		try {
//		    preparedStatement=con.prepareStatement("select * from 5mine_quack_results where type like '%"+dataString+"%'");
			preparedStatement = con.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("panfu", resultSet.getString(2));
				map.put("location", resultSet.getString(3));
				map.put("status", resultSet.getString(7));
				map.put("unused", resultSet.getString(8));
				map.put("used", resultSet.getString(9));
				map.put("total", resultSet.getString(10));
				map.put("netspeed", resultSet.getString(11));
				list.add(map);
			}
		} catch (Exception e) {
			System.out.println("连接失败");
			e.printStackTrace();
		}
		// 打印json
		String json = gson.toJson(list);
		System.out.println(json);
		return list;
	}

	// 按照地址＋刷新时间+盘符筛选sql语句
	private String sel_data_bytimeandpanfu(String spot_name, String refresh_time, String panfu) {
		// 设置初始值防止程序出现问题造成崩溃
		String db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='2020-11-15'\r\n;";
		// 红阳三矿入口
		if (spot_name.equals("hyks")) {
			System.out.println("进入红阳三矿");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n"
					+ "AND panfu='" + panfu + "'";
		}
		// 由于数据暂时缺失的缘故，遂当前仅使用红阳三矿的数据进行测试
		// 平顶山数据源入口
		else if (spot_name.equals("pds")) {
			System.out.println("进入平顶山矿井");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n"
					+ "AND panfu='" + panfu + "'";
			;
		}
		// 山西大同数据源入口
		else if (spot_name.equals("sxts")) {
			System.out.println("进入山西大同矿井");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n"
					+ "AND panfu='" + panfu + "'";
			;
		}
		// 双鸭山矿井数据源入口
		else {
			System.out.println("进入双鸭山矿井");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n"
					+ "AND panfu='" + panfu + "'";
			;
		}
		return db_nameString;
	}

	// 按照地址＋刷新时间筛选sql语句
	private String sel_data_bytime(String spot_name, String refresh_time) {
		// 设置初始值防止程序出现问题造成崩溃
		String db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='2020-11-15'\r\n;";
		// 红阳三矿入口
		if (spot_name.equals("hyks")) {
			System.out.println("进入红阳三矿");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n";
		}
		// 由于数据暂时缺失的缘故，遂当前仅使用红阳三矿的数据进行测试
		// 平顶山数据源入口
		else if (spot_name.equals("pds")) {
			System.out.println("进入平顶山矿井");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n";
		}
		// 山西大同数据源入口
		else if (spot_name.equals("sxts")) {
			System.out.println("进入山西大同矿井");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n";
		}
		// 双鸭山矿井数据源入口
		else {
			System.out.println("进入双鸭山矿井");
			db_nameString = "SELECT * FROM mine_quake_station_hongyang\r\n" + "WHERE day='" + refresh_time + "'\r\n";
		}
		return db_nameString;
	}
}
